package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.SmrzjlDao;
import com.bringway.p2p.service.SmrzjlService;

/**
 * 
 * @author qjy
 * @version 创建时间：2016年06月14日 上午13:25:42
 * @功能：查询异常信息
 */

@Repository
public class SmrzjlDaoImpl  extends P2PJdbcDao implements SmrzjlDao{
	Logger logger = Logger.getLogger(SmrzjlDaoImpl.class);
	
	
	public List<Map<String, Object>> querySmrzjlList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String username=params.optString("username");
		String sjhm=params.optString("sjhm");
		String rzzt=params.optString("rzzt");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("REAL_NAME", username);
		paramMap.put("SJHM", sjhm);
		paramMap.put("VERIFY_STATE", rzzt);
		sql.append(" select ID, USER_NAME,REAL_NAME,SJHM,SFZ_NUM,BANK_NAME,BANKCARD_NUM,VERIFY_QD,VERIFY_STATE,date_format(VERIFY_DATE,'%Y-%m-%d %H:%i:%s') VERIFY_DATE from t_zhfw_smrzjl ");
		sql.append(" where 1=1  ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(VERIFY_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(VERIFY_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND REAL_NAME like "+ "'%"+username+"%'");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(rzzt)){
			sql.append(" and VERIFY_STATE = :rzzt ");
			paramMap.put("rzzt", rzzt);
		}
		if(0 != limit){
			sql.append(" ORDER BY VERIFY_DATE DESC LIMIT :START,:LIMIT  ");
		}
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	@Override
	public int gettrzCount(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String rzzt=params.optString("rzzt");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("username", username);
		paramMap.put("sjhm", sjhm);
		paramMap.put("VERIFY_STATE", rzzt);
		sql.append(" SELECT count(*)  ");
		sql.append(" FROM t_zhfw_smrzjl ");
		sql.append("   where 1=1 ");
		if(StringUtils.isNotBlank(startDate)){
			sql.append(" AND date_format(VERIFY_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate", startDate+" 00:00:00");
		}
		if(StringUtils.isNotBlank(endDate)){
			sql.append(" AND date_format(VERIFY_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate", endDate+" 23:59:59");
		}
		
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND REAL_NAME like "+ "'%"+username+"%'");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(rzzt)){
			sql.append(" and VERIFY_STATE = :rzzt ");
			paramMap.put("rzzt", rzzt);
		}
		logger.info("gettjrCount sql is " + sql.toString());
		logger.info("gettjrCount paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}
	
	
	@Override
	public void delsmrzjl(JSONObject parms) {
		String Id = parms.optString("Id");
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from t_zhfw_smrzjl where id = :ID and VERIFY_STATE = '02' ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("ID", Id);
		logger.info("delsmrzjl sql is " + sql.toString());
		logger.info("delsmrzjl paramMap is " + parameters.toString());
		this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters);
	}
	@Override
	public Map<String, Object> getsmrzjl(JSONObject params) {
		String Id = params.optString("Id");
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from t_zhfw_smrzjl where id = :ID  ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("ID", Id);
		logger.info("getsmrzjl sql is " + sql.toString());
		logger.info("getsmrzjl paramMap is " + parameters.toString());
		if(this.getNamedParameterJdbcTemplate().queryForList(sql.toString(),parameters).size() > 0){
			return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), parameters);
		}else{
			return null;
		}
	}

	@Override
	public int backSmrzjlinfoById(String id) {
		String sql = "insert into t_zhfw_smrzjl_backups(ID, USER_NAME, REAL_NAME, SJHM, SFZ_NUM, BANK_NAME, BANKCARD_NUM, VERIFY_QD, VERIFY_STATE, VERIFY_DATE) select * from t_zhfw_smrzjl where ID=:id;";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("id", id);
		logger.info("backSmrzjlinfoById sql is " + sql);
		logger.info("backSmrzjlinfoById paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}
}
